/**
 * 
 */
package com.rhf.core.db.implementations.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.rhf.core.db.implementations.Base;
import com.rhf.core.db.interfaces.Competition;
import com.rhf.core.dto.CompetitionVO;

/**
 * @author fabiensaulnier
 * 
 */
public class CompetitionImp extends Base implements Competition {

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.rhf.core.db.interfaces.Competition#contructeur(java.sql.ResultSet)
	 */
	public CompetitionVO contructeur(ResultSet rs) throws Exception {

		CompetitionVO competitionVO = new CompetitionVO();

		try {

			competitionVO.setCompetitionId(rs.getInt("competition_id"));
			competitionVO.setDateCreation(rs.getTimestamp("date_creation"));
			competitionVO.setDateModification(rs.getTimestamp("date_modification"));
			competitionVO.setAuteurId(rs.getInt("auteur_id"));
			competitionVO.setSaisonId(rs.getInt("saison_id"));
			competitionVO.setPtsVictoire(rs.getInt("pts_victoire"));
			competitionVO.setPtsNul(rs.getInt("pts_nul"));
			competitionVO.setPtsDefaite(rs.getInt("pts_defaite"));
			competitionVO.setPtsForfait(rs.getInt("pts_forfait"));
			competitionVO.setDepartage1(rs.getInt("departage_1"));
			competitionVO.setDepartage2(rs.getInt("departage_2"));
			competitionVO.setDepartage3(rs.getInt("departage_3"));
			competitionVO.setCategorieId(rs.getInt("categorie_id"));
			competitionVO.setCompetitionParentId(rs.getInt("competition_parent_id"));
			competitionVO.setOrdre(rs.getInt("ordre"));
			competitionVO.setLibelle(rs.getString("libelle"));

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		}

		return competitionVO;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * @seecom.rhf.dao.interfaces.Competition#ajouterCompetition(com.rhf.core.db.
	 * valuesobjects.CompetitionVO)
	 */
	public int ajouterCompetition(CompetitionVO competitionVO) throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		int nbLigneAffecte = 0;

		// Requ�te SQL
		String query = "INSERT INTO competition ";
		query += "(date_creation, date_modification, auteur_id, saison_id, pts_victoire, pts_nul, pts_defaite, pts_forfait, departage_1, departage_2, departage_3, categorie_id, competition_parent_id, ordre, libelle) ";
		query += "VALUES (now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try {

			// Connexion � la base de donn�es
			conn = super.getConnection();
			stmt = conn.prepareStatement(query);

			// D�finition des param�tres (?)
			int i = 1;
			stmt.setInt(i++, competitionVO.getAuteurId());
			stmt.setInt(i++, competitionVO.getSaisonId());
			stmt.setInt(i++, competitionVO.getPtsVictoire());
			stmt.setInt(i++, competitionVO.getPtsNul());
			stmt.setInt(i++, competitionVO.getPtsDefaite());
			stmt.setInt(i++, competitionVO.getPtsForfait());
			stmt.setInt(i++, competitionVO.getDepartage1());
			stmt.setInt(i++, competitionVO.getDepartage2());
			stmt.setInt(i++, competitionVO.getDepartage3());
			stmt.setInt(i++, competitionVO.getCategorieId());
			stmt.setInt(i++, competitionVO.getCompetitionParentId());
			stmt.setInt(i++, competitionVO.getSaisonId());
			stmt.setString(i++, competitionVO.getLibelle());

			// Ex�cution de la requ�te
			this.appLog.debug(stmt.toString());
			nbLigneAffecte = stmt.executeUpdate();

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		} finally {
			super.closeResources(null, stmt, conn);
		}

		return nbLigneAffecte;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * 
	 * @seecom.rhf.dao.interfaces.Competition#modifierCompetition(com.rhf.core.db
	 * . valuesobjects.CompetitionVO)
	 */
	public int modifierCompetition(CompetitionVO competitionVO) throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		int nbLigneAffecte = 0;

		// Requ�te SQL
		String query = "UPDATE competition ";
		query += "SET date_modification = now(), auteur_id = ?, saison_id = ?, pts_victoire = ?, pts_nul = ?, pts_defaite = ?, pts_forfait = ?, departage_1 = ?, departage_2 = ?, departage_3 = ?, categorie_id = ?, competition_parent_id = ?, ordre = ?, libelle = ? ";
		query += "WHERE competition_id = ?";

		try {

			// Connexion � la base de donn�es
			conn = super.getConnection();
			stmt = conn.prepareStatement(query);

			// D�finition des param�tres (?)
			int i = 1;
			stmt.setInt(i++, competitionVO.getAuteurId());
			stmt.setInt(i++, competitionVO.getSaisonId());
			stmt.setInt(i++, competitionVO.getPtsVictoire());
			stmt.setInt(i++, competitionVO.getPtsNul());
			stmt.setInt(i++, competitionVO.getPtsDefaite());
			stmt.setInt(i++, competitionVO.getPtsForfait());
			stmt.setInt(i++, competitionVO.getDepartage1());
			stmt.setInt(i++, competitionVO.getDepartage2());
			stmt.setInt(i++, competitionVO.getDepartage3());
			stmt.setInt(i++, competitionVO.getCategorieId());
			stmt.setInt(i++, competitionVO.getCompetitionParentId());
			;
			stmt.setInt(i++, competitionVO.getSaisonId());
			stmt.setString(i++, competitionVO.getLibelle());

			// Ex�cution de la requ�te
			this.appLog.debug(stmt.toString());
			nbLigneAffecte = stmt.executeUpdate();

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		} finally {
			super.closeResources(null, stmt, conn);
		}

		return nbLigneAffecte;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.rhf.core.db.interfaces.Competition#recupererCompetition(int)
	 */
	public CompetitionVO recupererCompetition(int competitionId) throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		CompetitionVO competitionVO = null;

		// Requ�te SQL
		String query = "SELECT * ";
		query += "FROM competition";
		query += "WHERE competition_id = ?";

		try {

			// Connexion � la base de donn�es
			conn = super.getConnection();
			stmt = conn.prepareStatement(query);

			// D�finition des param�tres (?)
			stmt.setInt(1, competitionId);

			// Ex�cution de la requ�te
			this.appLog.debug(stmt.toString());
			rs = stmt.executeQuery();

			// Traitement des donn�es
			if (rs.next()) {
				competitionVO = contructeur(rs);
			}

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		} finally {
			super.closeResources(null, stmt, conn);
		}

		return competitionVO;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.rhf.core.db.interfaces.Competition#recupererListeCompetition()
	 */
	public List<CompetitionVO> recupererListeCompetition() throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<CompetitionVO> list = null;

		// Requ�te SQL
		String query = "SELECT * FROM competition ORDER BY ordre, libelle";

		try {

			// Connexion � la base de donn�es
			conn = super.getConnection();
			stmt = conn.prepareStatement(query);

			// Ex�cution de la requ�te
			this.appLog.debug(stmt.toString());
			rs = stmt.executeQuery();

			// Traitement des donn�es
			if (rs.next()) {
				list = new ArrayList<CompetitionVO>();
				list.add(contructeur(rs));
				while (rs.next()) {
					list.add(contructeur(rs));
				}
			}

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		} finally {
			super.closeResources(null, stmt, conn);
		}

		return list;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.rhf.core.db.interfaces.Competition#supprimerCompetition(int)
	 */
	public int supprimerCompetition(int competitionId) throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		int nbLigneAffecte = 0;

		// Requ�te SQL
		String query = "DELETE FROM competition ";
		query += "WHERE competition_id = ? ";

		try {

			// Connexion � la base de donn�es
			conn = super.getConnection();
			stmt = conn.prepareStatement(query);

			// D�finition des param�tres (?)
			stmt.setInt(1, competitionId);

			// Ex�cution de la requ�te
			this.appLog.debug(stmt.toString());
			nbLigneAffecte = stmt.executeUpdate();

		} catch (Exception e) {
			this.appLog.error(e.getMessage(), e);
			throw e;
		} finally {
			super.closeResources(null, stmt, conn);
		}

		return nbLigneAffecte;
	}

}
